﻿using Magicodes.ExporterAndImporter.Core.Extension;
using Newtonsoft.Json;
using NP.BPMReportPlatform.BPMPlatform;
using NP.BPMReportPlatform.DBHelper;
using NP.BPMReportPlatform.Entities.BPM_Business.CommonReportConfigs;
using NP.BPMReportPlatform.Reports.CommonReportConfigs.Dtos;
using NP.BPMReportPlatform.Utils;
using SqlSugar;
using SqlSugar.Extensions;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Volo.Abp.Application.Dtos;
using Scriban;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NP.BPMReportPlatform.Utilities;
using System.Linq.Dynamic.Core;
using Newtonsoft.Json.Linq;
using System.IO;
using System.Text.RegularExpressions;
using NP.BPMReportPlatform.Entities.BPM_Platform;
using System.Linq.Expressions;

namespace NP.BPMReportPlatform.Reports.CommonReportConfigs
{
    public class CommonReportService : BPMReportPlatformAppService, ICommonReportService
    {
        private readonly SqlSugarClient _db, _dbPlatform, _dbProcess, _dbEngine, _dbToDoCenter;
        private readonly IBPMUtilsAppService _bPMUtilsApp;
        private readonly IBPMPlatformAppService _iBPMPlatformApp;
        public CommonReportService(ISqlSugarAppService sqlSugarAppService, IBPMUtilsAppService bPMUtilsApp,
            IBPMPlatformAppService iBPMPlatformApp)
        {
            _db = sqlSugarAppService.GetBusinessInstance();
            _dbPlatform = sqlSugarAppService.GetPlatformInstance();
            _bPMUtilsApp = bPMUtilsApp;
            _dbProcess = sqlSugarAppService.GetProcessInstance();
            _dbEngine = sqlSugarAppService.GetEngineInstance();
            _dbToDoCenter = sqlSugarAppService.GetToDoCenterInstance();
            _iBPMPlatformApp = iBPMPlatformApp;
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PagedResultDto<object>> GetAllAsync(CommonReportInput input)
        {
            try
            {
                var module = await _db.Queryable<CommonReportModule>().FirstAsync(x => x.Id == input.ModuleId && x.IsEnabled);
                if (module == null)
                {
                    throw new Exception("缺少配置");
                }

                CheckFieldRequired(input);

                input.IsAll = false;
                var resData = module.ReportType == 1 ? await QueryDataAuto(input) : await QueryData(input);
                return new PagedResultDto<object>
                {
                    Items = resData.Item1,
                    TotalCount = resData.Item2
                };
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 校验必填项
        /// </summary>
        /// <param name="input"></param>
        /// <exception cref="Exception"></exception>
        private void CheckFieldRequired(CommonReportInput input)
        {
            input.Fields.ForEach(item =>
            {
                if (item.IsRequired && (item.FieldValue == null || string.IsNullOrWhiteSpace(item.FieldValue.ObjToString())))
                {
                    throw new Exception($"请填写必填项：{item.FieldDesc}");
                }
            });
        }

        private async Task<Tuple<List<object>, int>> QueryData(CommonReportInput input)
        {
            var scripts = await _db.Queryable<CommonReportCustomScripts>().Where(x => x.ModuleId == input.ModuleId).ToListAsync();
            var searchScript = scripts.FirstOrDefault(x => x.ScriptType == (int)ReportScriptEnum.Search)?.CustomScript;
            var countScript = scripts.FirstOrDefault(x => x.ScriptType == (int)ReportScriptEnum.Count)?.CustomScript;
            if (input.IsAll)
            {
                if (!scripts.Any(x => x.ScriptType == (int)ReportScriptEnum.Search))
                {
                    throw new Exception("缺少数据库脚本");
                }

                if (string.IsNullOrEmpty(searchScript))
                {
                    throw new Exception("缺少数据库脚本");
                }
            }
            else
            {
                if (!scripts.Any(x => x.ScriptType == (int)ReportScriptEnum.Search) || !scripts.Any(x => x.ScriptType == (int)ReportScriptEnum.Count))
                {
                    throw new Exception("缺少数据库脚本");
                }

                if (string.IsNullOrEmpty(searchScript) || string.IsNullOrEmpty(countScript))
                {
                    throw new Exception("缺少数据库脚本");
                }
            }

            var model = new Dictionary<string, object>();
            var sqlParameters = new List<SugarParameter>();
            input.Fields.ForEach(field =>
            {
                if (field.InputBoxType == "select-multiple" && !string.IsNullOrWhiteSpace(field.FieldValue.ObjToString()))
                {
                    var valueStr = field.FieldValue.ObjToString();
                    if (IsStringArray(valueStr))
                    {
                        model.Add(field.FieldName, JsonConvert.DeserializeObject<string[]>(valueStr));
                    }
                    else if(IsIntegerArray(valueStr))
                    {
                        model.Add(field.FieldName, JsonConvert.DeserializeObject<int[]>(valueStr));
                    }
                    else
                    {
                        model.Add(field.FieldName, null);
                    }
                }
                else
                {
                    model.Add(field.FieldName, field.FieldValue);
                }
                sqlParameters.Add(new SugarParameter(field.FieldName, field.FieldValue));
            });
            if (input.IsAll)
            {
                var sql = Template.Parse(searchScript).Render(model);
                var dataList = await _db.Ado.SqlQueryAsync<object>(sql, sqlParameters);
                return new Tuple<List<object>, int>(dataList, dataList.Count);
            }
            else
            {
                sqlParameters.Add(new SugarParameter("@PageIndex", input.PageSize * (input.PageIndex - 1)));
                sqlParameters.Add(new SugarParameter("@PageSize", input.PageSize));
                var sql = Template.Parse(searchScript).Render(model);
                var dataList = await _db.Ado.SqlQueryAsync<object>(sql, sqlParameters);
                var countSql = Template.Parse(countScript).Render(model);
                var totalCount = await _db.Ado.SqlQuerySingleAsync<int>(countSql, sqlParameters);                
                return new Tuple<List<object>, int>(dataList, totalCount);
            }
        }

        private async Task<Tuple<List<object>, int>> QueryDataAuto(CommonReportInput input)
        {
            var fields = await _db.Queryable<CommonReportFieldConfig>().Where(x => x.ModuleId == input.ModuleId).ToListAsync();
            var page = await GetPageByModuleId(input.ModuleId);
            if (page == null)
            {
                throw new Exception("没有绑定页面");
            }

            var tableFields = await _db.Queryable<CommonReportField>().Where(x => x.TABLE_SCHEMA == page.DbName && x.TABLE_NAME == page.TableName).ToListAsync();

            var tableName = page.TableName;// TODO 先只做单库单表查询
            var executeSql = new StringBuilder($"SELECT ");
            var countSql = new StringBuilder($"SELECT COUNT(*) FROM {tableName} WHERE 1=1 ");
            var sqlParameters = new List<SugarParameter>();

            var selectFields = fields.Where(x => x.IsShow || tableFields.Select(q => q.COLUMN_NAME).Contains(x.FieldName)).ToList();
            var selectFieldStrs = new List<string>();
            foreach (var field in selectFields)
            {
                selectFieldStrs.Add(!string.IsNullOrEmpty(field.CustomScript) ? field.CustomScript : field.FieldName);
            }
            foreach (var field in tableFields.Where(x => !selectFields.Select(q => q.FieldName).Contains(x.COLUMN_NAME)))
            {
                selectFieldStrs.Add(field.COLUMN_NAME);
            }
            executeSql.Append(selectFieldStrs.JoinAsString(","));
            executeSql.Append($" FROM {tableName} WHERE 1=1");

            var whereSql = new StringBuilder();
            foreach (var valueField in input.Fields)
            {
                var field = fields.FirstOrDefault(x => x.FieldName == valueField.FieldName);
                if (field == null || valueField.FieldValue == null || string.IsNullOrEmpty(field.FieldName) || !field.IsSearchItem)
                {
                    continue;
                }
                var tableField = tableFields.FirstOrDefault(x => x.COLUMN_NAME == field.FieldName);

                if (field.InputBoxType?.ToLower() == "date")
                {
                    var times = valueField.FieldValue.ToString().Split(" ~ ", StringSplitOptions.RemoveEmptyEntries);
                    if (times.Length >= 1)
                    {
                        if (tableField != null && CommonFieldHelper.GetCSharpTypeFromDbType(tableField.DATA_TYPE) == typeof(string))
                        {
                            whereSql.Append(@$" AND DATE_FORMAT({field.FieldName}, '%Y-%m-%d %H:%i:%s') <= @{field.FieldName}_EndTime AND DATE_FORMAT({field.FieldName}, '%Y-%m-%d %H:%i:%s') >= @{field.FieldName}_StartTime ");
                        }
                        else
                        {
                            whereSql.Append(@$" AND {field.FieldName} <= @{field.FieldName}_EndTime AND {field.FieldName} >= @{field.FieldName}_StartTime ");
                        }
                        sqlParameters.Add(new SugarParameter($"@{field.FieldName}_StartTime", times[0].ObjToDate()));
                        sqlParameters.Add(new SugarParameter($"@{field.FieldName}_EndTime", times[1].ObjToDate()));
                    }
                }
                else if (field.InputBoxType?.ToLower() == "select-multiple" && !string.IsNullOrWhiteSpace(valueField.FieldValue.ObjToString()))
                {
                    var SearchArr = JsonConvert.DeserializeObject<List<object>>(JsonConvert.SerializeObject(valueField.FieldValue));
                    whereSql.Append($" AND {field.FieldName} IN (@{field.FieldName}_SearchArr) ");
                    sqlParameters.Add(new SugarParameter($"@{field.FieldName}_SearchArr", SearchArr));
                }
                else if (!string.IsNullOrWhiteSpace(valueField.FieldValue.ObjToString()))
                {
                    whereSql.Append(field.IsAccurate ?
                        $" AND {field.FieldName}=@{field.FieldName}_SearchStr " : $" AND {field.FieldName} LIKE @{field.FieldName}_SearchStr ");
                    sqlParameters.Add(new SugarParameter($"@{field.FieldName}_SearchStr", field.IsAccurate ? valueField.FieldValue : $"%{valueField.FieldValue}%"));
                }
            }

            executeSql.Append(whereSql);
            countSql.Append(whereSql);

            var orderSql = new StringBuilder();
            var orderInputFields = fields.Where(x => !string.IsNullOrEmpty(x.OrderType)).OrderBy(x => x.OrderIndex).ToList();
            if (orderInputFields.Any())
            {
                orderSql.Append(" ORDER BY ");
                var orderStrs = new List<string>();
                foreach (var field in orderInputFields)
                {
                    orderStrs.Add($" {field.FieldName} {field.OrderType} ");
                }
                if (orderStrs.Any())
                {
                    orderSql.Append(orderStrs.JoinAsString(","));
                }
            }

            executeSql.Append(orderSql);
            if (!input.IsAll)
            {
                executeSql.Append($" LIMIT {input.PageSize} OFFSET {(input.PageIndex - 1) * input.PageSize} ");
            }

            var str = executeSql.ToString();
            var dataList = await _db.Ado.SqlQueryAsync<object>(executeSql.ToString(), sqlParameters);
            if (input.IsAll)
            {
                return new Tuple<List<object>, int>(dataList, dataList.Count);
            }
            else
            {
                var totalCount = (await _db.Ado.SqlQueryAsync<int>(countSql.ToString(), sqlParameters)).FirstOrDefault();
                return new Tuple<List<object>, int>(dataList, totalCount);
            }
        }

        private async Task<CommonReportPage> GetPageByModuleId(string moduleId)
        {
            if (string.IsNullOrEmpty(moduleId))
            {
                return null;
            }
            return await _db.Queryable<CommonReportPage, CommonReportPageModule>((a, b) => a.Id == b.PageId)
                .Where((a, b) => b.ModuleId == moduleId).Select((a, b) => a).FirstAsync();
        }

        private bool IsStringArray(string json)
        {
            try
            {
                JArray array = JArray.Parse(json);
                return array.All(t => t.Type == JTokenType.String);
            }
            catch
            {
                return false;
            }
        }

        private bool IsIntegerArray(string json)
        {
            try
            {
                JArray array = JArray.Parse(json);
                return array.All(t => t.Type == JTokenType.Integer);
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public async Task AddData(CommonReportInput input)
        {
            try
            {
                CheckFieldRequired(input);

                var module = await _db.Queryable<CommonReportModule>().FirstAsync(x => x.Id == input.ModuleId && x.IsEnabled);
                if (module == null)
                {
                    throw new Exception("缺少配置");
                }

                var configFields = await _db.Queryable<CommonReportFieldConfig>().Where(x => x.ModuleId == input.ModuleId).ToListAsync();
                if (!configFields.Any())
                {
                    throw new Exception("缺少字段配置");
                }

                var page = await GetPageByModuleId(input.ModuleId);
                if (page == null)
                {
                    throw new Exception("没有绑定页面");
                }

                var dbName = page.DbName;
                var tableName = page.TableName;
                if (string.IsNullOrEmpty(dbName) || string.IsNullOrEmpty(tableName))
                {
                    throw new Exception($"未定义表字段");
                }
                var fields = await _db.Queryable<CommonReportField>().Where(x => x.TABLE_SCHEMA == dbName && x.TABLE_NAME == tableName).ToListAsync();
                if (!fields.Any())
                {
                    throw new Exception($"{tableName}，表未初始化");
                }

                if (module.ReportType == 1)
                {
                    await AddOrUpdate(input);
                }
                else
                {
                    var scripts = await _db.Queryable<CommonReportCustomScripts>().Where(x => x.ModuleId == module.Id).ToListAsync();
                    if (!scripts.Any(x => x.ScriptType == (int)ReportScriptEnum.Insert) ||
                        scripts.Any(x => x.ScriptType == (int)ReportScriptEnum.Insert && string.IsNullOrWhiteSpace(x.CustomScript)))
                    {
                        throw new Exception("缺少数据库脚本");
                    }

                    var model = new Dictionary<string, object>();
                    var sqlParameters = new List<SugarParameter>();
                    input.Fields.ForEach(field =>
                    {
                        var tableField = fields.FirstOrDefault(x => x.TABLE_SCHEMA == dbName && x.TABLE_NAME == tableName && x.COLUMN_NAME == field.FieldName);
                        if (tableField != null && field.FieldValue == null && tableField.IS_NULLABLE == "NO")
                        {
                            field.FieldValue = CommonFieldHelper.GetTrueValueFromDbType(tableField.DATA_TYPE, tableField.COLUMN_DEFAULT, tableField.COLUMN_DEFAULT);
                        }
                        model.Add(field.FieldName, field.FieldValue);
                        sqlParameters.Add(new SugarParameter(field.FieldName, field.FieldValue));
                    });

                    var checkScripts = scripts.Where(x => x.ScriptType == (int)ReportScriptEnum.Check).ToList();
                    foreach (var script in checkScripts)
                    {
                        if (!string.IsNullOrWhiteSpace(script.CustomScript))
                        {
                            var checkSql = Template.Parse(script.CustomScript).Render(model);
                            var checkResult = await _db.Ado.SqlQuerySingleAsync<bool>(checkSql, sqlParameters);
                            if (checkResult)
                            {
                                throw new Exception(string.IsNullOrWhiteSpace(script.Message) ? "数据已存在" : Template.Parse(script.Message).Render(model));
                            }
                        }
                    }

                    var sqlScripts = scripts.Where(x => x.ScriptType == (int)ReportScriptEnum.Insert).ToList();
                    foreach (var script in sqlScripts)
                    {
                        if (!string.IsNullOrWhiteSpace(script.CustomScript))
                        {
                            var sql = Template.Parse(script.CustomScript).Render(model);
                            await _db.Ado.ExecuteCommandAsync(sql, sqlParameters);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public async Task UpdateData(CommonReportInput input)
        {
            try
            {
                CheckFieldRequired(input);

                var module = await _db.Queryable<CommonReportModule>().FirstAsync(x => x.Id == input.ModuleId && x.IsEnabled);
                if (module == null)
                {
                    throw new Exception("缺少配置");
                }

                var configFields = await _db.Queryable<CommonReportFieldConfig>().Where(x => x.ModuleId == input.ModuleId).ToListAsync();
                if (!configFields.Any())
                {
                    throw new Exception("缺少字段配置");
                }

                var page = await GetPageByModuleId(input.ModuleId);
                if (page == null)
                {
                    throw new Exception("没有绑定页面");
                }

                var dbName = page.DbName;
                var tableName = page.TableName;
                if (string.IsNullOrEmpty(dbName) || string.IsNullOrEmpty(tableName))
                {
                    throw new Exception($"未定义表字段");
                }
                var fields = await _db.Queryable<CommonReportField>().Where(x => x.TABLE_SCHEMA == dbName && x.TABLE_NAME == tableName).ToListAsync();
                if (!fields.Any())
                {
                    throw new Exception($"{tableName}，表未初始化");
                }
                if (!fields.Any(x => x.COLUMN_KEY == "PRI"))
                {
                    throw new Exception("表未定义主键");
                }

                if (module.ReportType == 1)
                {
                    await AddOrUpdate(input);
                }
                else
                {
                    var scripts = await _db.Queryable<CommonReportCustomScripts>().Where(x => x.ModuleId == module.Id).ToListAsync();
                    if (!scripts.Any(x => x.ScriptType == (int)ReportScriptEnum.Update) ||
                        string.IsNullOrEmpty(scripts.FirstOrDefault(x => x.ScriptType == (int)ReportScriptEnum.Update)?.CustomScript))
                    {
                        throw new Exception("缺少数据库脚本");
                    }

                    var model = new Dictionary<string, object>();
                    var sqlParameters = new List<SugarParameter>();
                    input.Fields.ForEach(field =>
                    {
                        var tableField = fields.FirstOrDefault(x => x.TABLE_SCHEMA == dbName && x.TABLE_NAME == tableName && x.COLUMN_NAME == field.FieldName);
                        if (tableField != null && field.FieldValue == null && tableField.IS_NULLABLE == "NO")
                        {
                            field.FieldValue = CommonFieldHelper.GetTrueValueFromDbType(tableField.DATA_TYPE, tableField.COLUMN_DEFAULT, tableField.COLUMN_DEFAULT);
                        }
                        model.Add(field.FieldName, field.FieldValue);
                        sqlParameters.Add(new SugarParameter(field.FieldName, field.FieldValue));
                    });

                    var checkScripts = scripts.Where(x => x.ScriptType == (int)ReportScriptEnum.Check).ToList();
                    foreach (var script in checkScripts)
                    {
                        if (!string.IsNullOrWhiteSpace(script.CustomScript))
                        {
                            var checkSql = Template.Parse(script.CustomScript).Render(model);
                            var checkResult = await _db.Ado.SqlQuerySingleAsync<bool>(checkSql, sqlParameters);
                            if (checkResult)
                            {
                                throw new Exception(string.IsNullOrWhiteSpace(script.Message) ? "数据已存在" : Template.Parse(script.Message).Render(model));
                            }
                        }
                    }

                    var priKeys = fields.Where(x => x.COLUMN_KEY == "PRI").Select(x => x.COLUMN_NAME).ToList();
                    var sqlScripts = scripts.Where(x => x.ScriptType == (int)ReportScriptEnum.Update).ToList();
                    foreach (var script in sqlScripts)
                    {
                        if (!string.IsNullOrWhiteSpace(script.CustomScript))
                        {
                            var sql = Template.Parse(script.CustomScript).Render(model);
                            var havePriKey = false;
                            foreach (var priKey in priKeys)
                            {
                                if (CheckIfWhereClauseContainsField(sql, priKey))
                                {
                                    havePriKey = true;
                                    break;
                                }
                            }
                            if (!havePriKey)
                            {
                                throw new Exception("未添加主键条件");
                            }

                            await _db.Ado.ExecuteCommandAsync(sql, sqlParameters);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 新增和更新，自动生成SQL模式
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public async Task AddOrUpdate(CommonReportInput input)
        {
            #region 数据校验

            var module = await _db.Queryable<CommonReportModule>().FirstAsync(x => x.Id == input.ModuleId && x.IsEnabled);
            if (module == null)
            {
                throw new Exception("无效查询");
            }

            var configFields = await _db.Queryable<CommonReportFieldConfig>().Where(x => x.ModuleId == input.ModuleId).ToListAsync();
            if (!configFields.Any())
            {
                throw new Exception("缺少字段配置");
            }

            var page = await GetPageByModuleId(input.ModuleId);
            if (page == null)
            {
                throw new Exception("没有绑定页面");
            }

            var dbName = page.DbName;
            var tableName = page.TableName;
            if (string.IsNullOrEmpty(dbName) || string.IsNullOrEmpty(tableName))
            {
                throw new Exception($"未定义表字段");
            }

            // 校验主键
            var fields = await _db.Queryable<CommonReportField>().Where(x => x.TABLE_SCHEMA == dbName && x.TABLE_NAME == tableName)
                .OrderBy(x => x.ORDINAL_POSITION).ToListAsync();
            var primarykeyField = fields.FirstOrDefault(x => x.COLUMN_KEY == "PRI");
            var primarykeyConfigField = primarykeyField == null ? null :
                configFields.FirstOrDefault(x => x.FieldName == primarykeyField.COLUMN_NAME);
            var inputKeyField = input.Fields.FirstOrDefault(q => q.FieldName == primarykeyConfigField.FieldName);
            if (primarykeyField == null || primarykeyConfigField == null || inputKeyField == null)
            {
                throw new Exception("没有配置主键");
            }

            var user = await _dbPlatform.Queryable<Users>().FirstAsync(x => x.WorkNumber.Equals(CurrentUser.UserName));

            // 字段校验
            foreach(var field in input.Fields)
            {
                if (field.DefaultType > 0)
                {
                    continue;
                }
                if (field.CheckType.HasValue && !string.IsNullOrWhiteSpace(field.FieldValue.ObjToString()))
                {
                    if (field.CheckType == 1) // 唯一校验
                    {
                        var checkSQL = new StringBuilder();
                        var count = 0;
                        if (inputKeyField.FieldValue == null)
                        {
                            checkSQL.Append($"SELECT COUNT(*) FROM {tableName} WHERE {field.FieldName}=@{field.FieldName}");
                            count = await _db.Ado.SqlQuerySingleAsync<int>(
                                checkSQL.ToString(),
                                new List<SugarParameter> {
                                    new SugarParameter($"@{field.FieldName}", field.FieldValue)
                                });
                        }
                        else
                        {
                            checkSQL.Append($"SELECT COUNT(*) FROM {tableName} WHERE {field.FieldName}=@{field.FieldName} AND {inputKeyField.FieldName}!=@{inputKeyField.FieldName}");
                            count = await _db.Ado.SqlQuerySingleAsync<int>(
                                checkSQL.ToString(),
                                new List<SugarParameter> {
                                    new SugarParameter($"@{field.FieldName}", field.FieldValue),
                                    new SugarParameter($"@{inputKeyField.FieldName}", inputKeyField.FieldValue)
                                });
                        }
                        if (count > 0)
                        {
                            throw new Exception($"{field.FieldDesc}，{(string.IsNullOrEmpty(field.CheckFailMessage) ? "数据已存在！" : field.CheckFailMessage)}");
                        }
                    }
                    else
                    {
                        var isMatch = RegexHelper.MatchSuccess(field.FieldValue.ObjToString(), field.RegexContent);
                        if (!isMatch)
                        {
                            throw new Exception($"{field.FieldDesc}，{(string.IsNullOrEmpty(field.CheckFailMessage) ? "校验失败！" : field.CheckFailMessage)}");
                        }
                    }
                }
            };

            #endregion

            if (inputKeyField.FieldValue == null)
            {
                //根据校验配置表，来校验数据，可以做到单个字段的校验，重复校验，字段精度校验，正则校验（邮箱，手机号码等）

                // 新增
                var addSql = new StringBuilder();
                var insertFields = new List<string>();
                var insertConditions = new List<string>();
                var sqlParameters = new List<SugarParameter>();
                foreach (var x in fields)
                {
                    if (x.EXTRA == "auto_increment")
                    {
                        continue;
                    }
                    var inputField = input.Fields.FirstOrDefault(q => q.FieldName == x.COLUMN_NAME);
                    if (inputField == null)
                    {
                        //throw new Exception($"字段{x.COLUMN_NAME}，缺少入参");
                        continue;
                    }
                    if (inputField.FieldName == inputKeyField.FieldName && string.IsNullOrWhiteSpace(inputKeyField.FieldValue.ObjToString()))
                    {
                        inputField.FieldValue = Guid.NewGuid().ToString();
                    }
                    var configField = configFields.FirstOrDefault(q => q.FieldName == x.COLUMN_NAME);
                    inputField.FieldValue = configField?.DefaultType > 0 ?
                        CommonFieldHelper.GetDefaultValue(configField.DefaultType.Value, configField.DefaultValue, x.DATA_TYPE, x.COLUMN_DEFAULT, user,
                            configField.InputBoxType == "date" ? configField.ShortSelectItemJson : "") :
                        CommonFieldHelper.GetTrueValueFromDbType(x.DATA_TYPE, inputField.FieldValue, x.COLUMN_DEFAULT);
                    insertFields.Add(x.COLUMN_NAME);
                    insertConditions.Add($"@{x.COLUMN_NAME}");
                    sqlParameters.Add(new SugarParameter($"@{x.COLUMN_NAME}", inputField.FieldValue));
                };

                addSql.Append($"INSERT INTO {tableName}({insertFields.JoinAsString(",")}) VALUES({insertConditions.JoinAsString(",")})");

                await _db.Ado.ExecuteCommandAsync(addSql.ToString(), sqlParameters);
            }
            else
            {
                // 修改
                var getEntitySql = $"SELECT * FROM {tableName} WHERE {inputKeyField.FieldName}=@{inputKeyField.FieldName}";
                var entities = await _db.Ado.SqlQueryAsync<object>(getEntitySql,
                    new List<SugarParameter> {
                        new SugarParameter ($"@{inputKeyField.FieldName}", inputKeyField.FieldValue)
                    });
                if (!entities.Any())
                {
                    throw new Exception("数据未找到");
                }
                if (entities.Count() > 1)
                {
                    throw new Exception("主键查询结果数据量不唯一");
                }

                var updateFields = configFields.Where(x => fields.Select(q => q.COLUMN_NAME).Contains(x.FieldName) && x.IsCanEdit).ToList();
                if (!updateFields.Any())
                {
                    throw new Exception("没有配置可编辑字段");
                }

                var updateSql = new StringBuilder();
                updateSql.Append($"UPDATE {tableName} SET ");
                var updateSqls = new List<string>();
                var updateParameters = new List<SugarParameter>();
                foreach (var field in updateFields)
                {
                    if (field.FieldName == inputKeyField.FieldName)
                    {
                        continue;
                    }
                    updateSqls.Add($" {field.FieldName}=@{field.FieldName} ");
                    var inputField = input.Fields.FirstOrDefault(q => q.FieldName == field.FieldName);
                    if (inputField == null)
                    {
                        //throw new Exception($"字段{field.FieldName}，缺少入参");
                        continue;
                    }
                    var tableField = fields.FirstOrDefault(x => x.COLUMN_NAME == field.FieldName);
                    if (tableField == null)
                    {
                        throw new Exception($"字段{field.FieldName}，未找到");
                    }
                    inputField.FieldValue = field.DefaultType > 0 ?
                        CommonFieldHelper.GetDefaultValue(field.DefaultType.Value, field.DefaultValue, tableField.DATA_TYPE, tableField.COLUMN_DEFAULT, user,
                            field.InputBoxType == "date" ? field.ShortSelectItemJson : "") :
                        CommonFieldHelper.GetTrueValueFromDbType(tableField.DATA_TYPE, inputField.FieldValue, tableField.COLUMN_DEFAULT);
                    updateParameters.Add(new SugarParameter($"@{field.FieldName}", inputField.FieldValue));
                }
                updateSql.Append(updateSqls.JoinAsString(","));
                updateSql.Append($" WHERE {inputKeyField.FieldName}=@{inputKeyField.FieldName} ");
                updateParameters.Add(new SugarParameter($"@{inputKeyField.FieldName}", inputKeyField.FieldValue));
                _db.Ado.ExecuteCommand(updateSql.ToString(), updateParameters);
            }
        }

        /// <summary>
        /// 删除数据，批量删除，传一个Ids数组
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task Delete(CommonReportInput input)
        {
            try
            {
                var module = await _db.Queryable<CommonReportModule>().FirstAsync(x => x.Id == input.ModuleId && x.IsEnabled);
                if (module == null)
                {
                    throw new Exception("缺少配置");
                }

                var configFields = await _db.Queryable<CommonReportFieldConfig>().Where(x => x.ModuleId == input.ModuleId).ToListAsync();
                if (!configFields.Any())
                {
                    throw new Exception("缺少字段配置");
                }

                var page = await GetPageByModuleId(input.ModuleId);
                if (page == null)
                {
                    throw new Exception("没有绑定页面");
                }

                var dbName = page.DbName;
                var tableName = page.TableName;
                if (string.IsNullOrEmpty(dbName) || string.IsNullOrEmpty(tableName))
                {
                    throw new Exception($"未定义表字段");
                }

                var fields = await _db.Queryable<CommonReportField>().Where(x => x.TABLE_SCHEMA == dbName && x.TABLE_NAME == tableName).ToListAsync();
                if (!fields.Any())
                {
                    throw new Exception($"表未初始化");
                }
                if (!fields.Any(x => x.COLUMN_KEY == "PRI"))
                {
                    throw new Exception("表未定义主键");
                }

                if (module.ReportType == 1)
                {
                    var primarykeyField = fields.FirstOrDefault(x => x.COLUMN_KEY == "PRI");

                    var primarykeyConfigField = primarykeyField == null ? null :
                        configFields.FirstOrDefault(x => x.FieldName == primarykeyField.COLUMN_NAME);

                    var inputKeyField = input.Fields.FirstOrDefault(q => q.FieldName == primarykeyConfigField.FieldName);

                    if (primarykeyField == null || primarykeyConfigField == null || inputKeyField == null)
                    {
                        throw new Exception("没有配置主键");
                    }

                    var getEntitySql = $"SELECT * FROM {tableName} WHERE {inputKeyField.FieldName}=@{inputKeyField.FieldName}";
                    var entities = await _db.Ado.SqlQueryAsync<object>(getEntitySql,
                        new List<SugarParameter> {
                        new SugarParameter ($"@{inputKeyField.FieldName}", inputKeyField.FieldValue)
                        });
                    if (!entities.Any())
                    {
                        throw new Exception("数据未找到");
                    }
                    if (entities.Count() > 1)
                    {
                        throw new Exception("主键查询结果数据量不唯一");
                    }

                    var delSql = $"DELETE FROM {tableName} WHERE {inputKeyField.FieldName}=@{inputKeyField.FieldName}";
                    _db.Ado.ExecuteCommand(delSql,
                        new List<SugarParameter> {
                        new SugarParameter ($"@{inputKeyField.FieldName}", inputKeyField.FieldValue)
                        });
                }
                else
                {
                    var scripts = await _db.Queryable<CommonReportCustomScripts>().Where(x => x.ModuleId == module.Id).ToListAsync();
                    if (!scripts.Any(x => x.ScriptType == (int)ReportScriptEnum.Delete))
                    {
                        throw new Exception("缺少数据库脚本");
                    }

                    var model = new Dictionary<string, object>();
                    var sqlParameters = new List<SugarParameter>();
                    input.Fields.ForEach(field =>
                    {
                        model.Add(field.FieldName, field.FieldValue);
                        sqlParameters.Add(new SugarParameter(field.FieldName, field.FieldValue));
                    });

                    var priKeys = fields.Where(x => x.COLUMN_KEY == "PRI").Select(x => x.COLUMN_NAME).ToList();
                    var deleteScripts = scripts.Where(x => x.ScriptType == (int)ReportScriptEnum.Delete).ToList();
                    foreach (var script in deleteScripts)
                    {
                        if (!string.IsNullOrWhiteSpace(script.CustomScript))
                        {
                            var sql = Template.Parse(script.CustomScript).Render(model);
                            var havePriKey = false;
                            foreach (var priKey in priKeys)
                            {
                                if (CheckIfWhereClauseContainsField(sql, priKey))
                                {
                                    havePriKey = true;
                                    break;
                                }
                            }
                            if (!havePriKey)
                            {
                                throw new Exception("未添加主键条件");
                            }

                            await _db.Ado.ExecuteCommandAsync(sql, sqlParameters);

                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        private bool CheckIfWhereClauseContainsField(string whereClause, string fieldName)
        {
            // 构建正则表达式，匹配字段名，忽略大小写和可能的空白字符
            string pattern = $@"\b{Regex.Escape(fieldName)}\b\s*";

            // 使用正则表达式检查WHERE子句
            return Regex.IsMatch(whereClause, pattern, RegexOptions.IgnoreCase);
        }

        /// <summary>
        /// 导出数据
        /// </summary>
        /// <returns></returns>
        public async Task<IWorkbook> ExportData(CommonReportInput input)
        {
            var module = await _db.Queryable<CommonReportModule>().FirstAsync(x => x.Id == input.ModuleId && x.IsEnabled);
            if (module == null)
            {
                throw new Exception("缺少配置");
            }

            var configFields = await _db.Queryable<CommonReportFieldConfig>().Where(x => x.ModuleId == module.Id).ToListAsync();

            try
            {
                var selectItemDic = new Dictionary<string, List<KeyValuePair<string, string>>>();
                configFields.ForEach(item =>
                {
                    if (item.InputBoxType == "select-single" || item.InputBoxType == "select-multiple")
                    {
                        var list = ConvertExtension.ConvertJsonToList(item.ShortSelectItemJson);
                        if (list.Any())
                        {
                            selectItemDic.Add(item.FieldName, list);
                        }
                    }
                });

                var workbook = new XSSFWorkbook();
                var sheet = workbook.CreateSheet("Sheet1");
                sheet.DisplayGridlines = true;
                IRow row = sheet.CreateRow(0); // 添加表头
                IFont headerRedFont = workbook.CreateFont();
                headerRedFont.Boldweight = (short)FontBoldWeight.Bold;
                headerRedFont.Color = IndexedColors.Red.Index;
                ICellStyle headerRedStyle = workbook.CreateCellStyle();
                headerRedStyle.SetFont(headerRedFont);
                headerRedStyle.VerticalAlignment = VerticalAlignment.Center;
                headerRedStyle.Alignment = HorizontalAlignment.Center;
                headerRedStyle.BorderBottom = BorderStyle.Thin;
                headerRedStyle.BorderLeft = BorderStyle.Thin;
                headerRedStyle.BorderRight = BorderStyle.Thin;
                headerRedStyle.BorderTop = BorderStyle.Thin;

                IFont headerFont = workbook.CreateFont();
                headerFont.Boldweight = (short)FontBoldWeight.Bold;
                ICellStyle headerStyle = workbook.CreateCellStyle();
                headerStyle.SetFont(headerFont);
                headerStyle.VerticalAlignment = VerticalAlignment.Center;
                headerStyle.Alignment = HorizontalAlignment.Center;
                headerStyle.BorderBottom = BorderStyle.Thin;
                headerStyle.BorderLeft = BorderStyle.Thin;
                headerStyle.BorderRight = BorderStyle.Thin;
                headerStyle.BorderTop = BorderStyle.Thin;

                IFont dataFont = workbook.CreateFont();
                dataFont.Boldweight = (short)FontBoldWeight.Normal;
                ICellStyle dataStyle = workbook.CreateCellStyle();
                dataStyle.SetFont(dataFont);
                dataStyle.VerticalAlignment = VerticalAlignment.Center;
                dataStyle.Alignment = HorizontalAlignment.Center;
                dataStyle.BorderBottom = BorderStyle.Hair;
                dataStyle.BorderLeft = BorderStyle.Hair;
                dataStyle.BorderRight = BorderStyle.Hair;
                dataStyle.BorderTop = BorderStyle.Hair;

                var headers = input.IsTemplate.HasValue && input.IsTemplate.Value ?
                    _db.Queryable<CommonReportFieldConfig>().Where(x => x.ModuleId == input.ModuleId && x.IsCanImport).OrderBy(x => x.ShowIndex).ToList() :
                    _db.Queryable<CommonReportFieldConfig>().Where(x => x.ModuleId == input.ModuleId && x.IsCanExport).OrderBy(x => x.ShowIndex).ToList();
                for (var i = 0; i < headers.Count; i++)
                {
                    sheet.SetColumnWidth(i, 15 * 256);
                    row.CreateCell(i).SetCellValue(headers[i].FieldDesc);
                    row.GetCell(i).CellStyle = headers[i].IsRequired && input.IsTemplate.HasValue && input.IsTemplate.Value ? headerRedStyle : headerStyle;
                }

                if (input.IsTemplate.HasValue && input.IsTemplate.Value)
                {
                    return workbook;
                }

                input.IsAll = true;
                var resData = module.ReportType == 1 ? await QueryDataAuto(input) : await QueryData(input);
                if (resData.Item1.Any())
                {
                    var rowIndex = 1;
                    var jArray = JArray.FromObject(resData.Item1);
                    foreach (var data in jArray)
                    {
                        IRow rowtemp = sheet.CreateRow(rowIndex);
                        var columnIndex = 0;
                        var rowData = (JObject)data;
                        foreach (var column in headers)
                        {
                            var fieldValue = rowData.ContainsKey(column.FieldName) ? rowData[column.FieldName].ObjToString() : "";
                            var configField = configFields.FirstOrDefault(x => x.FieldName == column.FieldName);
                            if (configField != null && !string.IsNullOrWhiteSpace(fieldValue))
                            {
                                if (configField.InputBoxType == "date")
                                {
                                    var timeFormat = string.IsNullOrEmpty(configField.ShortSelectItemJson) ? DateFormatHelper.DateTimeFormat : configField.ShortSelectItemJson;
                                    fieldValue = rowData[column.FieldName].ObjToDate().ToString(timeFormat);
                                }
                                else if(configField.InputBoxType == "select-single" && selectItemDic.ContainsKey(configField.FieldName))
                                {
                                    var haveValue = selectItemDic[configField.FieldName].Count(x => x.Key.Equals(fieldValue)) > 0;
                                    fieldValue = haveValue ? selectItemDic[configField.FieldName].FirstOrDefault(x => x.Key.Equals(fieldValue)).Value : fieldValue;
                                }
                                else if (configField.InputBoxType == "select-multiple" && selectItemDic.ContainsKey(configField.FieldName))
                                {
                                    var vals = fieldValue.Split(",");
                                    var valList = new List<string>();
                                    foreach (var val in vals)
                                    {
                                        var haveValue = selectItemDic[configField.FieldName].Count(x => x.Key.Equals(val)) > 0;
                                        var value = haveValue ? selectItemDic[configField.FieldName].FirstOrDefault(x => x.Key.Equals(val)).Value : val;
                                        valList.Add(value);
                                    }
                                    fieldValue = valList.JoinAsString(",");
                                }
                            }

                            rowtemp.CreateCell(columnIndex).SetCellValue(fieldValue);
                            rowtemp.GetCell(columnIndex).CellStyle = dataStyle;
                            columnIndex++;
                        }

                        rowIndex++;
                    }
                }

                return workbook;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 导入数据
        /// </summary>
        /// <param name="fileStream"></param>
        /// <param name="moduleId"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public async Task<List<string>> ImportData(byte[] bytes, string moduleId)
        {
            Stream stream = new MemoryStream(bytes);
            var res = new List<string>();

            var module = await _db.Queryable<CommonReportModule>().FirstAsync(x => x.Id == moduleId && x.IsEnabled);
            if (module == null)
            {
                res.Add("缺少配置");
                return res;
            }

            var configFields = await _db.Queryable<CommonReportFieldConfig>().Where(x => x.ModuleId == moduleId && x.IsCanImport)
                .OrderBy(x => x.ShowIndex).ToListAsync();
            if (!configFields.Any())
            {
                res.Add("缺少配置");
                return res;
            }

            var page = await GetPageByModuleId(moduleId);
            if (page == null)
            {
                throw new Exception("没有绑定页面");
            }

            var dbName = page.DbName;
            var tableName = page.TableName;
            if (string.IsNullOrEmpty(dbName) || string.IsNullOrEmpty(tableName))
            {
                throw new Exception($"未定义表字段");
            }

            var fields = await _db.Queryable<CommonReportField>().Where(x => x.TABLE_SCHEMA == dbName && x.TABLE_NAME == tableName).ToListAsync();
            if (!fields.Any())
            {
                throw new Exception($"{tableName}，表未初始化");
            }

            var user = await _dbPlatform.Queryable<Users>().FirstAsync(x => x.WorkNumber.Equals(CurrentUser.UserName));

            if (module.ReportType == 1)
            {
                #region 数据校验

                // 校验主键
                var primarykeyField = fields.FirstOrDefault(x => x.COLUMN_KEY == "PRI");
                if (primarykeyField == null)
                {
                    throw new Exception("没有配置主键");
                }

                #endregion

                try
                {
                    var rst = new ImportCommonReportDataHelper(stream);

                    var importData = rst.ConvertSheetToList(configFields, fields, user).ToList();

                    var queryAllSql = new StringBuilder();
                    queryAllSql.Append($"SELECT * FROM {tableName}");
                    var dataList = await _db.Ado.SqlQueryAsync<dynamic>(queryAllSql.ToString()); // 查出现有所有数据
                    var dataDic = new List<Dictionary<string, object>>();
                    dataList.ForEach(item => dataDic.Add(ConvertDynamicDictionary(item)));

                    var index = 2;
                    foreach (var row in importData)
                    {
                        // 字段校验
                        foreach (var rowField in row)
                        {
                            var field = fields.FirstOrDefault(x => x.COLUMN_NAME == rowField.FieldName);
                            if (field == null)
                            {
                                continue;
                            }

                            if (rowField.IsRequired && (rowField.FieldValue == null || string.IsNullOrWhiteSpace(rowField.FieldValue.ObjToString())))
                            {
                                throw new Exception($"第{index}行，请填写必填项：{rowField.FieldDesc}");
                            }

                            if (rowField.CheckType.HasValue && !string.IsNullOrWhiteSpace(rowField.FieldValue.ObjToString()))
                            {
                                if (rowField.CheckType == 1) // 唯一校验
                                {
                                    var count = importData.Count(x => x.Count(q => q.FieldName == rowField.FieldName && q.FieldValue == rowField.FieldValue) > 0);
                                    if (count > 1)
                                    {
                                        throw new Exception($"第{index}行，{rowField.FieldDesc}，{(string.IsNullOrEmpty(rowField.CheckFailMessage) ? "数据重复！" : rowField.CheckFailMessage)}");
                                    }
                                    count = dataDic.SelectMany(x => x.Where(q => q.Key == rowField.FieldName && Equals(q.Value, rowField.FieldValue))).Count();
                                    if (count > 0)
                                    {
                                        throw new Exception($"第{index}行，{rowField.FieldDesc}，{(string.IsNullOrEmpty(rowField.CheckFailMessage) ? "数据已存在！" : rowField.CheckFailMessage)}");
                                    }
                                }
                                else
                                {
                                    var isMatch = RegexHelper.MatchSuccess(rowField.FieldValue.ObjToString(), rowField.RegexContent);
                                    if (!isMatch)
                                    {
                                        throw new Exception($"第{index}行，{rowField.FieldDesc}，{(string.IsNullOrEmpty(rowField.CheckFailMessage) ? "校验失败！" : rowField.CheckFailMessage)}");
                                    }
                                }
                            }
                        }

                        index++;
                    }

                    try
                    {
                        _db.BeginTran();

                        foreach (var row in importData)
                        {
                            // 新增
                            var addSql = new StringBuilder();
                            var insertFields = new List<string>();
                            var insertConditions = new List<string>();
                            var sqlParameters = new List<SugarParameter>();
                            foreach (var x in fields)
                            {
                                if (x.EXTRA == "auto_increment")
                                {
                                    continue;
                                }
                                var rowField = row.FirstOrDefault(q => q.FieldName == x.COLUMN_NAME);
                                if (x.COLUMN_KEY == "PRI")
                                {
                                    insertFields.Add(x.COLUMN_NAME);
                                    insertConditions.Add($"@{x.COLUMN_NAME}");
                                    if (rowField == null)
                                    {
                                        sqlParameters.Add(new SugarParameter($"@{x.COLUMN_NAME}", Guid.NewGuid().ToString()));
                                    }
                                    else
                                    {
                                        sqlParameters.Add(new SugarParameter($"@{x.COLUMN_NAME}",
                                            string.IsNullOrWhiteSpace(rowField.FieldValue.ObjToString()) ? Guid.NewGuid().ToString() : rowField.FieldValue));
                                    }
                                    continue;
                                }
                                if (rowField == null)
                                {
                                    if (x.IS_NULLABLE == "NO")
                                    {
                                        throw new Exception($"字段{x.COLUMN_NAME}，缺少入参");
                                    }
                                    else
                                    {
                                        continue;
                                    }
                                }
                                insertFields.Add(x.COLUMN_NAME);
                                insertConditions.Add($"@{x.COLUMN_NAME}");
                                var fieldValueDefault = CommonFieldHelper.GetTrueValueFromDbType(x.DATA_TYPE, x.COLUMN_DEFAULT, x.COLUMN_DEFAULT);
                                sqlParameters.Add(new SugarParameter($"@{x.COLUMN_NAME}", rowField == null ? fieldValueDefault : rowField.FieldValue));
                            };

                            addSql.Append($"INSERT INTO {tableName}({insertFields.JoinAsString(",")}) VALUES({insertConditions.JoinAsString(",")})");

                            await _db.Ado.ExecuteCommandAsync(addSql.ToString(), sqlParameters);
                        }

                        _db.CommitTran();
                    }
                    catch (Exception e)
                    {
                        _db.RollbackTran();

                        throw new Exception(e.Message);
                    }

                    return res;
                }
                catch (Exception ex)
                {
                    res.Add(ex.Message);
                    return res;
                }
            }
            else
            {
                var scripts = await _db.Queryable<CommonReportCustomScripts>().Where(x => x.ModuleId == moduleId).ToListAsync();
                if (!scripts.Any(x => x.ScriptType == (int)ReportScriptEnum.Insert) ||
                    scripts.Any(x => x.ScriptType == (int)ReportScriptEnum.Insert && string.IsNullOrWhiteSpace(x.CustomScript)))
                {
                    throw new Exception("缺少数据库脚本");
                }

                try
                {
                    var rst = new ImportCommonReportDataHelper(stream);

                    var importData = rst.ConvertSheetToList(configFields, fields, user).ToList();

                    var checkScripts = scripts.Where(x => x.ScriptType == (int)ReportScriptEnum.Check).ToList();
                    var insertScripts = scripts.Where(x => x.ScriptType == (int)ReportScriptEnum.Insert).ToList();

                    var index = 2;
                    foreach (var row in importData)
                    {
                        var model = new Dictionary<string, object>();
                        var sqlParameters = new List<SugarParameter>();
                        foreach (var field in row)
                        {
                            model.Add(field.FieldName, field.FieldValue);
                            sqlParameters.Add(new SugarParameter(field.FieldName, field.FieldValue));
                        }

                        foreach (var s in checkScripts)
                        {
                            if (!string.IsNullOrWhiteSpace(s.CustomScript))
                            {
                                var checkSql = Template.Parse(s.CustomScript).Render(model);
                                var checkResult = await _db.Ado.SqlQuerySingleAsync<bool>(checkSql, sqlParameters);
                                if (checkResult)
                                {
                                    res.Add($"第{index}行，" + (string.IsNullOrWhiteSpace(s.Message) ? "数据已存在" : Template.Parse(s.Message).Render(model)));
                                    return res;
                                }
                            }
                        }

                        index++;
                    }

                    try
                    {
                        _db.BeginTran();

                        foreach (var row in importData)
                        {
                            var model = new Dictionary<string, object>();
                            var sqlParameters = new List<SugarParameter>();
                            row.ForEach(field =>
                            {
                                model.Add(field.FieldName, field.FieldValue);
                                sqlParameters.Add(new SugarParameter(field.FieldName, field.FieldValue));
                            });

                            foreach (var script in insertScripts)
                            {
                                if (!string.IsNullOrWhiteSpace(script.CustomScript))
                                {
                                    var sql = Template.Parse(script.CustomScript).Render(model);
                                    await _db.Ado.ExecuteCommandAsync(sql, sqlParameters);
                                }
                            }
                        }

                        _db.CommitTran();
                    }
                    catch (Exception e)
                    {
                        _db.RollbackTran();

                        throw new Exception(e.Message);
                    }

                    return res;
                }
                catch (Exception ex)
                {
                    res.Add(ex.Message);
                    return res;
                }
            }
        }

        private bool EqualObj(object obj1, object obj2)
        {
            bool areEqual = false;

            if (obj1 == null && obj2 == null)
            {
                areEqual = true;  // 两个都为 null，认为相等
            }
            else if (obj1 != null && obj2 != null)
            {
                if (obj1.GetType() == obj2.GetType())
                {
                    areEqual = obj1.Equals(obj2);  // 类型相同，内容比较
                }
                else if (obj1 is string str1 && obj2 is int int2)
                {
                    // 例如，obj1 是 string，obj2 是 int，将 string 转为 int 比较
                    areEqual = int.TryParse(str1, out int parsedValue) && parsedValue == int2;
                }
                // 其他类型比较逻辑
            }

            return areEqual;
        }

        private Dictionary<string, object> ConvertDynamicDictionary(dynamic dynamicObject)
        {
            // 将动态对象转换为字典
            var dictionary = new Dictionary<string, object>();
            foreach (var property in (IDictionary<string, object>)dynamicObject)
            {
                dictionary.Add(property.Key, property.Value);
            }
            return dictionary;
        }

        /// <summary>
        /// 获取页面配置
        /// </summary>
        /// <returns></returns>
        public async Task<object> GetPageConfigs(string pageId)
        {
            var page = await _db.Queryable<CommonReportPage>().FirstAsync(x => (x.Id == pageId || x.PageCode == pageId) && x.IsEnabled);
            if (page == null || string.IsNullOrEmpty(page.DbName) || string.IsNullOrEmpty(page.TableName))
            {
                throw new Exception("页面无效");
            }

            var modules = await _db.Queryable<CommonReportModule, CommonReportPageModule>((x, c) => x.Id == c.ModuleId && c.PageId == page.Id)
                .Where((x, c) => x.IsEnabled).Select(x => x).ToListAsync();
            if (!modules.Any())
            {
                throw new Exception("没有配置模块");
            }

            var configFields = await _db.Queryable<CommonReportFieldConfig>().Where(x => modules.Select(q => q.Id).Contains(x.ModuleId)).ToListAsync();
            if (!configFields.Any())
            {
                throw new Exception("没有配置字段");
            }

            var fields = await _db.Queryable<CommonReportField>().Where(x => x.TABLE_SCHEMA == page.DbName && x.TABLE_NAME == page.TableName).ToListAsync();
            if (!fields.Any())
            {
                throw new Exception("没有初始化表");
            }           

            configFields.ForEach(item =>
            {
                item.Field = fields.FirstOrDefault(x => x.TABLE_SCHEMA == page.DbName && x.TABLE_NAME == page.TableName && x.COLUMN_NAME == item.FieldName);
            });
            modules.ForEach(item =>
            {
                item.ConfigFields = configFields.Where(x => x.ModuleId == item.Id).OrderBy(x => x.ShowIndex).ToList();
            });

            return new
            {
                Page = page,
                Modules = modules,
            };
        }
    }
}
